from data_access.read_db import execute_sql, execute_select
from data_access.db_conn import engine
import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, Column, String, Float

# 读取CSV文件
csv_file_path = '../../data-source/Data-Copilot/Educations/ListingofCentreServices.csv'
df = pd.read_csv(csv_file_path)

# 定义数据库表的名称
table_name = 'preschool_centre_service'

# 创建表结构（如果尚不存在）
metadata = MetaData()
table = Table(
    table_name, metadata,
    Column('centre_name', String(255)),
    Column('centre_code', String(255)),
    Column('class_of_licence', String(255)),
    Column('type_of_service', String(255)),
    Column('levels_offered', String(255)),
    Column('fees', String(255)),
    Column('type_of_citizenship', String(255)),
)

# 创建表（如果不存在）
metadata.create_all(engine)


# 准备插入数据的SQL语句
insert_query = f'INSERT INTO {table_name} (centre_name,centre_code,class_of_licence,type_of_service,levels_offered,' \
               f'fees, type_of_citizenship ) VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s")'

# 插入数据
for index, row in df.iterrows():
    if 1:
        sql = insert_query % (row['centre_name'], row['centre_code'],row['class_of_licence'], row['type_of_service'],
                              row['levels_offered'], row['fees'], row['type_of_citizenship'])
        execute_sql(sql)

print("Data has been written to the database.")

